Skip to content

태그: SQL 쿼리

SQL에서 Alias(별칭)은 컬럼이나 테이블에 임시 이름을 주는 용도로 사용한다. 컬럼이나 테이블 뒤에 한번 띄어쓰기를 한 후 적거나, 컬럼 뒤에 AS를 붙인 뒤 적어주면 된다. Alias를 지정하면 컬럼/테이블명 대신 해당 별칭을 사용할 수 있다. (FROM절에서서 테이블블 앨리어스를를 지정할떄는 AS를 사용하지 못한다.) SELECT 별칭1.컬럼1 AS '컬럼 별칭1', 별칭1.컬럼2 AS '컬럼 별칭2', 별칭1.컬럼3 AS '컬럼 별칭3'FROM 테이블1 '별칭1' AS를 생략하고 띄어쓰기만 해줘도 Alias를 지정할 수 있다. SELECT 컬럼1 '컬럼 별칭1', 컬럼2 '컬럼 별칭2', 컬럼3 '컬럼 별칭3'FROM 테이블1 '별칭1' 컬럼을 지칭하는

Delete(/Modify) Action Cascade: Master 삭제 시 Child 같이 삭제 Set Null: Master 삭제 시 Child의 FK 필드 Null set Default: Master 삭제 시 Child FK 필드 Default 값으로 설정 Restrict: Child 테이블에 PK 값이 없는 경우만 Master 삭제 허용 No Action: 참조 무결성을 위반하는 삭제/수정 액션을 취하지 않음(MySQL에서는 Restrict와 동일함) Insert Action Automatic: Master 테이블에 PK가 없는 경우 Master PK를 생성 후 Child Set Null: Master 테이블에 PK가 없는 경우 Child 외부키를 Null 값으로 처리 Set Default:

GROUPING SETS와 GROUPING

GROUPING SETS 인자별 소계를 계산한다. SELECT 종, 성별, SUM(*) AS 마릿수FROM 반려동물GROUP BY GROUPING SETS(종, 성별); 결과 종성별마릿수강아지(null)10고양이(null)12앵무새(null)7(null)F15(null)M14 GROUPING GROUPING은 ROLLUP이나 CUBE, GROUPING SETS 등의 그룹함수에 의해 컬럼 값이 소계나 총합과 같이 집계된 데이터일 경우 1을 리턴하고 만약 집계된 데이터가 아니면 0을 리턴하는 함수이다. WHEN THEN문과 같이 사용하여 아래와 같이 응용할 수 있다. SELECT 종, 성별, SUM(*) AS 마릿수,

GROUP BY와 HAVING절

GROUP BY GROUP BY절은 GROUP BY절에 기재한 컬럼을 기춘으로 결과 집합을 그룹화한다. 이 GROUP BY절을 사용하면 소그룹별 기준 칼럼을 정한 후, 집계 함수를 사용하여 그룹별 통계를 계산할 수 있다. GROUP BY절 쿼리 예제 코드 SELECT A.species, SUM(A.count) AS "마릿수"FROM animal AWHERE A.birth_day = '2022%'GROUP BY A.species; 결과 species | 마릿수--------|--------강아지 | 45고양이 | 40앵무새 | 15 HAVING절 HAVING절은 그룹을 나타내는 결과집합의 행에 조건이 적용된다. HAVING절에는 GROUP BY절의 기준 항목이나 소그룹의 집계함수가 조건으로

INNER JOIN과 OUTER JOIN

JOIN은 두 테이블의 데이터를 합치는 것을 의미한다. INNER JOIN과 OUTER JOIN은 그 방식에 차이가 있다. 중복 컬럼을 가지고 있지 않은 A와 B 테이블을 JOIN한다고 가정할때, 일반적으로 INNER JOIN은 A와 B의 교집합, 즉 벤다이어그램으로 그렸을 떄 교차되는 부분이 결과로 추출되고, OUTER JOIN은 A와 B의 합집합, 즉 벤타이어그램으로 그렸을 때 두 부분의 전체가 결과로 추출된다. (단, Left Outer Join과 Right Outer Join은 완전히 합집합은 아니다. 밑에서 예제로 더 알아보자) 두개의 테이블이 있다고 가정해보자 A B -1 32 43 54 6 Inner Join Inner join을 하면 두 집합에서 공통되는

On절은 조인문을 사용할때 조인할 조건을 설정하기 위한 명령어이다. 조회 결과를 필터링한다는 점에서 Where절과 비슷하지만, 실행 시기와 용도에 차이가 있다. On절은 Where절과 다르게 테이블 조인 이전에 실행된다. 그렇기 때문에 Join할 테이블의 일부 컬럼을 가져오고 싶을 때 ON절을 사용한다. 그러나 Inner join을 사용하는 경우엔 조건의 위치와 테이블의 순서에 무관하게 사용할 수 있다. 왜냐하면 조인하는 두 테이블의 위치관계나, 쿼리 순서에 상관없이 결과가 똑같이 교집합으로 나오기 때문이다. On절은, Outer join을 사용할 떄 의미를 가진다. 두개의 테이블이 있다고 가정해보자. A B -1 32 43 54 6 아래의 쿼리로 Left Outer Jo

ORDER BY절은 SELECT문에서 조회한 데이터 집합을 특정 칼럼 기준으로 정렬한 후 데이터를 출력하는 역할을 한다. 오름차순(ASC) 또는 내림차순(DESC)로 정렬방식을 지정할 수 있다. 정렬방식을 지정하지 않으면 기본적으로 오름차순이 적용된다. 정렬은 일반적으로 숫자에서는 작은 숫자가 먼저, 날짜형 데이터에서는 이른 시간이 먼저, 문자형 데이터에서는 사전순으로 앞서는 문자열이 먼저로 간주된다. Oracle DBMS에서는 NULL값을 가장 큰 값으로 간주하여 오름차순으로 정렬했을 경우에는 가장 마지막에, 내림차순으로 정렬헀을 경우에는 가장 먼저 위치한다는 특징이 있다. ORDER BY절 쿼리 예제 SELECT *FROM 테이블명1 AORDER BY A.컬럼명1

ROLLUP 전체 학생 테이블에서 SELECT COUNT(*)FROM studentGROUP BY (grade) 와 같이 데이터를 조회하면 1학년, 2학년, 3학년 각 학생의 인원 수가 조회되는데, 여기서 GROUP BY를 GROUP BY ROLLUP (grade)로 변경하면 각 학년별 인원수와 전체 인원수까지 모두 집계해준다. SELECT student.grade AS 학년, COUNT(*) AS 인원수FROM studentGROUP BY ROLLUP(grade) 결과 학년인원수1학년722학년803학년80(null)232 ROLLUP에 여러 인자를 넣는 경우에는, 인자를 넣는 순서에 따라 결과가 바뀔 수 있다. CUBE CUBE 함수는 그룹핑 컬럼이

SELECT쿼리 실행순서

계층형 질의

계층형 질의란 테이블에 계층형 데이터가 존재하는 경우 데이터를 조회하기 위한 구문이다. 계층형 데이터는 동일 테이블에 상위와 하위 데이터가 포함된 데이터를 뜻하고, 엔티티를 순환관계 모델로 설계하는 경우 발생한다. 계층형 질의 사용 방법 쿼리구문설명START WITH계층구조 전개의 시작 위치를 지정하는 구문이다. 루트 데이터를 지정한다.CONNECT BY다음에 전개될 자식 데이터를 지정한다. 자식 데이터는 CONNECT BY 절에 주어진 조건을 만족해야한다.(JOIN)PRIORCONNECT BY 절에 사용되며 현재 읽은 칼럼을 지정한다.PRIOR 자식부모형태를 사용하면 계층구조에서 부모 -자식 방향으로 순방향 전개PRIOR 부모자식 형

서브쿼리란 하나의 SQL문 안에 포함되어 있는 또 다른 SQL문을 말한다. 조인은 조인에 참여하는 모든 테이블이 대등한 관계에 있기 때문에, 조인에 참여하는 모든 테이블의 컬럼을 어디에서든 자유롭게 사용(참조)할 수 있지만, 서브퀴리는 메인쿼리에서 따로 파생되어 생긴 쿼리이기 떄문에 자유로운 참조가 불가능하다. 서브쿼리는 메인쿼리의 컬럼을 모두 사용할 수 있지만, 메인 쿼리는 서브쿼리의 컬럼을 사용할 수 없다. 메인쿼리는 서브쿼리에게 자신의 컬럼을 줄 수 있지만, 서브쿼리의 컬럼을 사용할 순 없다. 반대로 서브쿼리는 메인쿼리가 가지고 있는 컬럼을 사용할 수 있지만, 컬럼을 줄 순 없다. 서브쿼리 사용 위치 서브쿼리 사용이 가능한 위치는 다음과 같다. 똑같은 서브쿼리라도 위치에 따라 다른 명칭으로 불리기도 한

윈도우 함수

SELECT문을 통한 작업을 하다보면 결과집합의 각 행과 행의 관계에서 다양한 연산처리를 해야할 떄가 있다. (누적합계, 누적비율, 누적등수 등) 이러한 연산처리를 할 수 있게 하는 것이 바로 윈도우 함수(Window Function)이다. 분석함수라고 불리기도 한다. 윈도우 함수의 종류 순위 함수명설명RANK지정한 기준에 따라 순위를 구하고, 동일한 순위가 있다면 건너뛰고 다음 순위로 산출한다. (1, 2, 2, 4)DENSE_RANK지정한 기준에 따라 순위를 구하고, 동일한 순위가 있다면 건너뛰지 않고 다음 순위로 산출한다. (1, 2, 2, 3)ROW_NUMBER지정한 기준에 따라 순위를 구하고, 동일 순위가 있어도 무조건 순위를 산출한다. (1, 2, 3, ,4)

제약조건은 테이블에 입력되는 데이터가 사용자가 원하는 조건을 만족하는 데이터만 입력되는 것을 보장한다. 제양조건은 데이터의 무결정을 유지하기 위한 DBMS의 보편적인 방법이다. 제약조건설명PRIMARY KEY테이블에 저장된 행들 중에서 특정 행을 고유하게 식별하기 위해서 사용한다.한 테이블에는 하나의 기본키만 정의할 수 있다.기본키 생성 시 DBMS는 유일 인덱스(Unique index)를 자동으로 생성한다.기본키 칼럼에는 NULL 입력이 불가능하다.기본키는 UNIQUE제약조건과 NOT NULL 제약조건을 만족해야한다.UNIQUE KEY테이블에 저장된 행들 중에서 특정 행을 고유하게 식별하기 위해 생성한다.기본키와 다르게 NULL 입력이 가능하다.N

집계함수를 이용하면 그룹별 집계 결과를 계산하여 한 행으로 나타낼 수 있다. 대표적인 집계함수는 아래와 같은 것들이 있다. 항목결과COUNT(*)NULL 값을 포함한 행의 수를 출력한다.COUNT(표현식)표현식의 값이 NULL이 아닌 행의 수를 출력한다.SUM(표현식)표현식이 NULL 값인 것을 제외한 합계를 출력한다.AVG(표현식)표현식이 NULL 값인 것을 제외한 평균을 출력한다.MAX(표현식)표현식이 NULL 값인 것을 제외한 최대값을 출력한다.MIN(표현식)표현식이 NULL 값인 것을 제외한 최솟값을 출력한다.STDDEV(표현식)표현식이 NULL 값인 것을 제외한 출력한다.VARIAN(표현식)표현식이 NULL 값인 것을 제외한

집합연산자

일반적으로 수학에서 사용되는 집합연산으로는 합집합, 교칩한, 차집합, 곱집합등이 있다. SQL문에서도 각각의 일반집합연산에 해당하는 명령어가 존재한다. 일반집합연산자SQL문설명합집합(UNION 연산)UNION, UNION ALLUNION 연산은 수학적으로 합집합을 하는 연산이다.UNION은 교집합의 중복을 제거한 결과를 나타내기 때문에, 정렬 작업으로 인한 시스템 부하가 일어날 수 있다.UNION ALL을 쓰면 중복 결과를 그대로 보여준다.만일 UNION과 UNION ALL의 출력 결과가 같다면 응답속도 향상, 자원 효율화 측면에서 UNION ALL을 쓰는것이 더 낫다.교집합(INTERSECTION 연산)INTERSECTINTERSECTION은 수학의 교

DB 커넥션 풀

일반적인 데이터 연동과정은 웹 어플리케이션이 필요할 때마다 데이터베이스에 연결하여 작업하는 방식이다. 하지만 이런 식으로 필요할 때마다 연동해서 작업할 경우 데이터베이스 연결에 시간이 많이 걸리는 문제가 발생한다. 예를들어 거래소의 경우, 동시에 몇천명이 동시에 거래 및 조회 기능을 사용하는데 매번 데이터베이스와 커넥션을 맺고 푸는 작업을 한다면 굉장히 비효율적일 것이다. 이 문제를 해결하기 위해 현재는 웹 어플리케이션이 실행됨과 동시에 연동할 데이터베이스와의 연결을 미리 설정해 두고, 필요할 때마다 미리 연결해 놓은 상태를 이용해 빠르게 데이터베이스와 연동하여 작업하는 방식을 사용한다. 이렇게 미리 데이터베이스와 연결시킨 상태를 유지하는 기술을 커넥션 풀(Connection Pool, CP)이라고 한다.

옵티마이저

옵티마이저는 사용자가 요청한 SQL문에 대한 최적의 실행 방법을 결정하는 역할을 수행한다. 이떄, 옵티마이저가 도출한 실행 방법을 실행계획이라고 한다. 사용자의 요구사항을 만족하는 결과를 추출할 수 있는 다양한 실행 방법들을 도출한 후, 그중에서 최적의 실행 방법을 결정하는 것이 옵티마이저의 역할이다. 비절차형 언어를 사용하는 경우 필요한 요소중 하나이다. 옵티마이저가 실행 계획을 모색하는 방법에는 룰 기반과 비용 기반이 있다. 룰 기반은 정해진 규칙에 따라 SQL문을 도출하는 방식이고, 비용기반은 다양한 DBMS의 객체정보 및 통계정보를 활용하여 최적의 실행 계획을 도출하는 방법이다. 룰 기반에 비해선 비용기반이 상황에 맞게 효율적인 쿼리를 생성할 수 있기 때문에, 오라클 10 이후 버전부터는 공식적으로

조인 수행원리

조인이란 2개 이상의 테이블을 하나의 집합으로 만드는 연산이다. FROM 절에 2개 이상의 테이블 혹은 집합이 존재할 경우 조인이 수행된다. 조인은 3개 이상의 테이블을 조인한다고 하더라도 특정 시점에는 2개의 테이블 단위로 조인된다. A, B, C 테이블을 조인한다고 하면 A, B를 조인한 후 해당 결과로 나온 집합을 C와 조인하는 방식이다. 각각의 조인 단계에서는 서로 다른 조인 기법이 사용될 수 있다. 조인 기법 조인 기법의 종류에는 NL 조인, 소트 머지 조인, 해시 조인이 있다. NL 조인 (Nested Loops Join) NL 조인은 첫 번째 집합의 대상 건수만큼 반복하면서 두 번째 집합을 조회하여 매칭되는 행을 리턴하는 조인 기법입니다. 한 레코드(행)씩 순차적으로 진행하기 때문에 처리 범위가

DCL은 데이터 제어어를 뜻한다. 데이터베이스에 접근하고 객체들을 사용하도록 권한을 부여하고 회수하는 데 사용한다. 구분설명GRANT데이터베이스에 접근하고 객체를 사용하는 권한을 줄 때 사용한다.REVOKEGRANT로 준 권한을 회수할 때 사용한다.

DDL은 데이터 정의어를 뜻한다. DB를 구성하는 물리적 객체(사용자, 테이블, 인덱스, 뷰, 트리거, 프로시저, 사용자 정의 함수 등)을 정의/변경/제거하는 데 사용한다. 테이블의 무결성 보장을 위해 제약조건을 설정할 수도 있다. 구분설명CREATE객체를 생성할때 사용한다.ALTER객체를 수정할때 사용한다.DROP객체를 삭제할때 사용한다. DDL 쿼리 예제 테이블 생성 CREATE문을 통해 테이블을 생성하는 쿼리이다. CREATE TABLE 테이블명1( 컬럼명1 DATATYPE, 컬럼명2 DATATYPE, CONSTRAINT 제약조건명1 PRIMARY KEY(컬럼명1)); SELECT문으로 테이블을 생성하는 방법도 있다. (테이블 구조와 일부

DML은 데이터 조작어를 뜻한다. 테이블의 데이터를 입력/수정/삭제/조회하는 데 사용한다. 호스트 프로그램속에 삽입되어 사용되는 DML 명령어는 데이터 부속어(DSL, Data SubLanguage)라고 부르기도 한다. 구분설명INSERT테이블에 데이터를 신규로 입력할 때 사용한다.UPDATE테이블 내 행의 칼럼값을 수정할 때 사용한다.DELETE테이블 내의 행을 삭제할 때 사용한다.SELECT테이블에서 데이터를 조회할 때 사용한다. DML 쿼리 예제 신규 데이터 입력 INSERT INTO 테이블명1(컬럼명1, 컬럼명2)VALUES('데이터1','데이터2'); 데이터 조회 SELECT 테이블명1.컬럼명1, 테이블명1.컬럼명2FROM 테이블명1

Procedural／Nonprocedural DML

DML은 사용자가 DB에서 원하는 데이터를 처리할 수 있도록 명세하기 위한 도구이다. 간단하게 말하면 테이블의 데이터를 입력/수정/삭제/조회하는 데 쓰이는 쿼리 명령어라고 할 수 있다. DML은 데이터 처리를 명세하는 방법에 따라 두가지 유형으로 나눌 수 있다. 절차적 데이터 조작어(Procedural DML) 절차적 데이터 조작어는 사용자가 무슨 데이터(What)를 원하며, 그것을 어떻게(How) 접근하여 처리할지 명세해야하는 초급 데이터 언어이다. 이런 데이터 조작어는 데이터 베이스로부터 한 번에 하나의 레코드(One-record-at-a-time)를 검색해서 호스트 언어(해당 응용 프로그램을 작성하는데 사용된 범용 프로그래밍 언어)와 함께 처리하는 특성을 가지고 있다. 비절차적 데이터 조작어(Nonpr

TCL은 트랜잭션 제어어를 뜻한다. 트랜잭션이란 DB에서 처리되는 논리적인 연산 단위를 뜻하는데, TCL은 데이터의 변경 후 커밋, 롤백으로 트랜잭션을 완료, 취소하는 작업을 수행할 때 사용한다. 주의점 단, 아래의 경우에는 TCL 명령어와 상관없이 트랜잭션 적용 처리가 일어난다. DDL문이 실행되었을 경우(이전에 실행됐던 DML문도 함께 커밋된다) DB에 대한 접속을 정상적으로 종료한 경우 위와 같은 상황에서는 트랜잭션이 비정상적으로 처리될 위험이 있으므로 데이터베이스 사용 시 꼭 주의해야한다. TCL 쿼리 예제 커밋 COMMIT; 롤백 ROLLBACK; 세이브포인트 SAVEPOINT SVPT; 세이브포인트까지 롤백 ROLLBACK TO SVPT;